﻿using eShop.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace eShop.RDBSStrategy.SqlServer
{
    /// <summary>
    /// 接口实现
    /// </summary>
    public partial class RDBSStrategy : IRDBSStrategy
    {

        #region 仓库字典信息
        /// <summary>
        /// 创建仓库
        /// </summary>
        /// <param name="StockInfo"></param>
        /// <returns></returns>
        public int CreateStock(StockInfo StockInfo)
        {
            string commandText = string.Format("insert into {0}Stock(Stockbh,Stockmc) values('{1}','{2}')", RDBSHelper.RDBSTablePre, StockInfo.StockBh, StockInfo.StockMc);
            return RDBSHelper.ExecuteNonQuery(commandText);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="StockInfo"></param>
        public void UpdateStock(StockInfo StockInfo)
        {
            string commandText = string.Format("update {0}Stock set Stockmc = '{1}' where Stockid = {2}", RDBSHelper.RDBSTablePre, StockInfo.StockMc, StockInfo.StockId);
            RDBSHelper.ExecuteNonQuery(commandText);
        }
        /// <summary>
        /// 删除仓库数据
        /// </summary>
        /// <param name="wid"></param>
        /// <returns></returns>
        public int DeleteStockById(int wid)
        {
            //判断仓库是否使用
            string commandText = string.Format("select count(1) from {0}productstocks where stockid={1}", RDBSHelper.RDBSTablePre, wid);
            object oret = RDBSHelper.ExecuteScalar(CommandType.Text, commandText);
            int ret = TypeHelper.ObjectToInt(oret);
            if (ret > 0)
            {
                return -1;
            }
            else
            {
                commandText = string.Format("delete from {0}Stock where Stockid={1}", RDBSHelper.RDBSTablePre, wid);
                return RDBSHelper.ExecuteNonQuery(commandText);
            }
        }
        /// <summary>
        /// 得到仓库数据列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetStockList()
        {
            string commandText = string.Format("select * from {0}Stock", RDBSHelper.RDBSTablePre);
            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0];
        }
        #endregion


        #region 入库信息
        /// <summary>
        /// 得到入库单列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetReceiveList()
        {
            string commandText = string.Format(@"select a.*,b.stockmc,c.name as pname from {0}receive a 
                                                 left outer join {0}stock  b on a.stockid = b.stockid
                                                 left outer join {0}products c on a.pid = c.pid", RDBSHelper.RDBSTablePre);
            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0];
        }

        /// <summary>
        /// 删除入库单（根据id)
        /// </summary>
        /// <param name="receiveid"></param>
        /// <returns></returns>
        public int DeleteReceiveById(int receiveid)
        {
            SqlConnection conn = new SqlConnection(RDBSHelper.ConnectionString);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    ReceiveInfo _info = null;
                    IDataReader reader = eShop.Core.BSPData.RDBS.GetReceiveById(receiveid);
                    if (reader.Read())
                    {
                        _info = new ReceiveInfo();
                        _info.ReceiveId = receiveid;
                        _info.PId = TypeHelper.ObjectToInt(reader["pid"]);
                        _info.StockId = TypeHelper.ObjectToInt(reader["stockid"]);
                        _info.ReceiveNum = TypeHelper.ObjectToInt(reader["receivenum"]);
                    }
                    reader.Close();
                    //1、先找出原来的数量 减掉
                    string commandText = string.Format("update {0}productstocks set number = number - {1} where pid ={2} and stockid={3}", RDBSHelper.RDBSTablePre, _info.ReceiveNum, _info.PId, _info.StockId);
                    RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                    //2、删除入库单
                    commandText = string.Format("delete from {0}receive where receiveId={1}", RDBSHelper.RDBSTablePre, receiveid);
                    RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                }
            }
            conn.Close();

            return 1;

        }

        /// <summary>
        /// 更新入库单信息（更新时注意productstocks中的商品库存数量
        /// </summary>
        /// <param name="ReceiveInfo"></param>
        public int UpdateReceive(ReceiveInfo ReceiveInfo)
        {

            SqlConnection conn = new SqlConnection(RDBSHelper.ConnectionString);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    ReceiveInfo _info = null;
                    IDataReader reader = eShop.Core.BSPData.RDBS.GetReceiveById(ReceiveInfo.ReceiveId);
                    if (reader.Read())
                    {
                        _info = new ReceiveInfo();
                        _info.PId = TypeHelper.ObjectToInt(reader["pid"]);
                        _info.StockId = TypeHelper.ObjectToInt(reader["stockid"]);
                        _info.ReceiveNum = TypeHelper.ObjectToInt(reader["receivenum"]);
                    }
                    reader.Close();
                    //0、判断库存表中是否有该数据
                    string commandText = string.Format("select count(1) from [{0}productstocks] where pid = {1} and stockid = {2}", RDBSHelper.RDBSTablePre, _info.PId, _info.StockId);
                    if (TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(trans, CommandType.Text, commandText)) <= 0)
                    {
                        //新增库存表
                        commandText = string.Format("insert into {0}productstocks(pid,stockid,number,limit) values({1},{2},{3},{4})", RDBSHelper.RDBSTablePre, _info.PId, _info.StockId, ReceiveInfo.ReceiveNum, ReceiveInfo.ReceiveNum);
                        RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                    }
                    else
                    {
                        //1、先找出原来的数量 减掉
                        commandText = string.Format("update {0}productstocks set number = number - {1} where pid ={2} and stockid={3}", RDBSHelper.RDBSTablePre, _info.ReceiveNum, _info.PId, _info.StockId);
                        RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                        //3、更新数量
                        commandText = string.Format("update {0}productstocks set number = number + {1} where pid ={2} and stockid={3}", RDBSHelper.RDBSTablePre, ReceiveInfo.ReceiveNum, ReceiveInfo.PId, ReceiveInfo.StockId);
                        RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                    }
                    //2、更新入库单
                    commandText = string.Format("update {0}receive set receiveno='{1}',receivedate='{2}',stockid={3},pid={4},receivenum={5} where receiveId={6}", RDBSHelper.RDBSTablePre, ReceiveInfo.ReceiveNo, ReceiveInfo.ReceiveDate, ReceiveInfo.StockId, ReceiveInfo.PId, ReceiveInfo.ReceiveNum, ReceiveInfo.ReceiveId);
                    RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);

                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return -1;
                }
            }
            conn.Close();

            return 1;
        }

        /// <summary>
        /// 创建入库单
        /// </summary>
        /// <param name="ReceiveInfo"></param>
        /// <returns></returns>
        public int CreateReceive(ReceiveInfo ReceiveInfo)
        {
            SqlConnection conn = new SqlConnection(RDBSHelper.ConnectionString);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    DbParameter[] parms = {
                        GenerateInParam("@receiveno",SqlDbType.NVarChar,20,ReceiveInfo.ReceiveNo),
                        GenerateInParam("@receivedate",SqlDbType.DateTime,4,ReceiveInfo.ReceiveDate),
                        GenerateInParam("@stockid",SqlDbType.Int,4,ReceiveInfo.StockId),
                        GenerateInParam("@pid",SqlDbType.Int,4,ReceiveInfo.PId),
                        GenerateInParam("@receivenum",SqlDbType.Int,4,ReceiveInfo.ReceiveNum),
                        GenerateInParam("@doperson",SqlDbType.VarChar,20,ReceiveInfo.DoPerson),
                        GenerateInParam("@remark",SqlDbType.VarChar,100,ReceiveInfo.Remark)
                    };
                    //1、创建入库单
                    string commandText = string.Format("insert into {0}receive(receiveno,receivedate,stockid,pid,receivenum,doperson,remark) values(@receiveno,@receivedate,@stockid,@pid,@receivenum,@doperson,@remark)", RDBSHelper.RDBSTablePre, parms);
                    if (RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText, parms) > 0)
                    {
                        //判断商品库存是否存在
                        commandText = string.Format("select count(1) from {0}productstocks where pid={1} and stockid = {2}", RDBSHelper.RDBSTablePre, ReceiveInfo.PId, ReceiveInfo.StockId);
                        if (TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText)) > 0)
                        {
                            //2、更新商品库存
                            commandText = string.Format("update {0}productstocks set [number] = [number] + {1} where stockid = {2} and pid ={3}", RDBSHelper.RDBSTablePre, ReceiveInfo.ReceiveNum, ReceiveInfo.StockId, ReceiveInfo.PId);
                            RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                        }
                        else
                        {
                            //插入
                            commandText = string.Format("insert into {0}productstocks(pid,stockid,number,limit) values({1},{2},{3},{4})", RDBSHelper.RDBSTablePre, ReceiveInfo.PId, ReceiveInfo.StockId, ReceiveInfo.ReceiveNum, 0);
                            RDBSHelper.ExecuteNonQuery(trans, CommandType.Text, commandText);
                        }

                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return -1;
                }
            }
            conn.Close();

            return 1;

        }



        public IDataReader GetReceiveById(int rid)
        {
            DbParameter[] parms =  {
                                        GenerateInParam("@receiveId", SqlDbType.Int, 4, rid)
                                    };

            string commandText = string.Format("select * from {0}receive where receiveId =@receiveId", RDBSHelper.RDBSTablePre);
            return RDBSHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        #endregion

    }
}
